/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package inet.dao;

import inet.entity.Employee;
import inet.util.Md5;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import inet.entity.Branch;
import inet.entity.Department;
import inet.entity.Position;
import java.math.BigDecimal;
//import java.sql.Date;
;
//import oracle.jrockit.jfr.events.DynamicValueDescriptor;


/**
 *
 * @author ÂuNgọcThái
 */
public class UserDAO extends BaseDAO {

    // lấy thông tin tài khoản theo tên đăng nhập và mật khẩu
    public Employee login(String username, String password) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        try {
            sql = "SELECT EMP_ID, EMP_CODE, FIRST_NAME,LAST_NAME, EMP_MOBILE, EMP_EMAIL, ADDRESS, EMP_DOB, BIRTH_PLACE, "
                    + "GENDER, NATIONALITY, LITERACY, ROLL,ACC_NAME, STATUS, AVATAR "
                    + "FROM EMPLOYEE WHERE ACC_NAME=? AND PASSWORD=?";
            String pwdMd5 = Md5.Hash(password);
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, username);
            ps.setString(2, pwdMd5);

            rs = ps.executeQuery();
            Employee emp = null;
            if (rs.next()) {
                emp = new Employee();
                int i = 1;
                emp.setEmp_id(rs.getBigDecimal(i++));
                emp.setEmp_code(rs.getString(i++));
                emp.setFirst_name(rs.getString(i++));
                emp.setLast_name(rs.getString(i++));
                emp.setEmp_mobile(rs.getString(i++));
                emp.setEmp_email(rs.getString(i++));
                emp.setAddress(rs.getString(i++));
                emp.setEmp_dob(rs.getDate(i++));
                emp.setBirth_place(rs.getString(i++));
                emp.setGender(rs.getInt(i++));
                emp.setNationality(rs.getString(i++));
                emp.setLyteracy(rs.getString(i++));
                emp.setRoll(rs.getInt(i++));
                emp.setAcc_name(rs.getString(i++));
                emp.setStatus(rs.getInt(i++));
                emp.setAvatar(rs.getString(i++));

            }
            return emp;
        } catch (Exception ex) {
            System.out.println("Error in sql: " + sql);
            ex.printStackTrace();
        } finally {
            releaseConnection(conn, ps, rs);
        }
        return null;
    }

    public boolean updateByEmp(Employee e) {
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = null;
        try {
            int i = 1;
            sql = "UPDATE EMPLOYEE SET FIRST_NAME=?,LAST_NAME=?,EMP_MOBILE=?,EMP_EMAIL=?,ADDRESS=?,"
                    + " BIRTH_PLACE=?, GENDER=?, NATIONALITY=?,LITERACY=?,AVATAR=?"
                    + " WHERE EMP_ID=?";
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(i++, e.getFirst_name());
            ps.setString(i++, e.getLast_name());
            ps.setString(i++, e.getEmp_mobile());
            ps.setString(i++, e.getEmp_email());
            ps.setString(i++, e.getAddress());
            //ps.setDate(i++, (java.sql.Date) e.getEmp_dob());
            ps.setString(i++, e.getBirth_place());
            ps.setInt(i++, e.getGender());
            ps.setString(i++, e.getNationality());
            ps.setString(i++, e.getLyteracy());
            ps.setString(i++, e.getAvatar());
            ps.setBigDecimal(i++, e.getEmp_id());
            return ps.executeUpdate() == 1;

        } catch (Exception ex) {
            System.out.println("Error in sql: " + sql);
            ex.printStackTrace();
        } finally {
            releaseConnection(conn, ps);
        }
        return false;
    }

    public boolean changePassword(Employee e) {
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = null;
        String pwdMd5 = Md5.Hash(e.getPassword());
        try {
            sql = "UPDATE EMPLOYEE SET PASSWORD=? WHERE EMP_ID=?";
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, pwdMd5);
            ps.setBigDecimal(2, e.getEmp_id());
            return ps.executeUpdate() == 1;

        } catch (Exception ex) {
            System.out.println("Error in sql: " + sql);
            ex.printStackTrace();
        } finally {
            releaseConnection(conn, ps);
        }
        return false;
    }

    public int checkPassword(BigDecimal id, String p) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;

        try {
            sql = "SELECT COUNT(EMP_ID) FROM EMPLOYEE WHERE EMP_ID=? AND PASSWORD=?";
            String pwdMd5 = Md5.Hash(p);
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            ps.setBigDecimal(1, id);
            ps.setString(2, pwdMd5);

            rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (Exception ex) {
            System.out.println("Error in sql: " + sql);
            ex.printStackTrace();
        } finally {
            releaseConnection(conn, ps, rs);
        }
        return 0;
    }

    public Position showPosionEmp(BigDecimal id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        try {
            sql = "SELECT POSITION_ID, EMP_ID, COM_ID,COM_POSITION,BRANCH_ID,BRANCH_POSITION,DEP_ID,DEP_POSITION FROM POSITION WHERE EMP_ID=? ";
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            ps.setBigDecimal(1, id);
            rs = ps.executeQuery();
            Position pos = null;
            if (rs.next()) {
                pos = new Position();
                int i = 1;
                pos.setPosition_id(rs.getBigDecimal(i++));
                pos.setEmp_id(rs.getBigDecimal(i++));
                pos.setCom_id(rs.getBigDecimal(i++));
                pos.setCom_position(rs.getString(i++));
                pos.setBranch_id(rs.getBigDecimal(i++));
                pos.setBranch_position(rs.getString(i++));
                pos.setDep_id(rs.getBigDecimal(i++));
                pos.setDep_position(rs.getString(i++));
                //System.out.print(pos.getBranch_id());
            }
            return pos;
        } catch (Exception ex) {
            System.out.println("Error in sql: " + sql);
            ex.printStackTrace();
        } finally {
            releaseConnection(conn, ps, rs);
        }
        return null;
    }

    public Branch showBranchEmp(BigDecimal id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        try {
            sql = "SELECT BRANCH_ID ,BRANCH_NAME FROM BRANCH WHERE BRANCH_ID=?  ";
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            ps.setBigDecimal(1, id);
            rs = ps.executeQuery();
            Branch br = null;
            if (rs.next()) {
                br = new Branch();
                int i = 1;
                br.setBranch_id(rs.getBigDecimal(i++));
                br.setBranch_name(rs.getString(i++));
                //System.out.println(br.getBranch_name());
            }
            return br;
        } catch (Exception ex) {
            System.out.println("Error in sql: " + sql);
            ex.printStackTrace();
        } finally {
            releaseConnection(conn, ps, rs);
        }
        return null;
    }

    public Department showDepEmp(BigDecimal id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        try {
            sql = "SELECT DEP_ID ,DEP_NAME FROM DEPARTMENTS WHERE DEP_ID=?  ";
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            ps.setBigDecimal(1, id);
            rs = ps.executeQuery();
            Department dep = null;
            if (rs.next()) {
                dep = new Department();
                int i = 1;
                dep.setDep_id(rs.getBigDecimal(i++));
                dep.setDep_name(rs.getString(i++));
            }
            return dep;
        } catch (Exception ex) {
            System.out.println("Error in sql: " + sql);
            ex.printStackTrace();
        } finally {
            releaseConnection(conn, ps, rs);
        }
        return null;
    }
    
}
